/*
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 2.1 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */
package org.teremail.store.jdbc3;

import static org.teremail.schema.DBTypes.BLOB;
import static org.teremail.schema.DBTypes.VARCHAR;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import javax.sql.DataSource;
import javax.sql.rowset.serial.SerialBlob;

import org.teremail.common.Log;
import org.teremail.schema.Dialect;
import org.teremail.schema.Schema;
import org.teremail.schema.Table;
import org.teremail.schema.TableBuilder;
import org.teremail.store.Store;
import org.teremail.store.StoreException;
import org.teremail.util.DB;


/**
 * A JDBC3 Implemenation of a store that provides a backing
 * for the stream store component.
 * 
 * @author Michael Barker <mailto:mike@middlesoft.co.uk>
 * @version $Revision: 1.6 $
 */
public class JDBC3Store implements Store {
    
    private final static Log log = Log.getLog(JDBC3Store.class);
    private final static Table BLOB_STORE_TABLE;
    private final DataSource ds;
    private final Dialect dialect;
    
    static {
        TableBuilder tb = new TableBuilder("BLOB_STORE");
        tb.column("ID", VARCHAR(40)).primaryKey();
        tb.column("DATA", BLOB()).nullable(false);
        BLOB_STORE_TABLE = tb.build();
    }

    public JDBC3Store(DataSource ds, Dialect dialect) {
        this.ds = ds;
        this.dialect = dialect;
    }

    /**
     * Initalise the JDBC3 Store.  Create the store
     * table if required.
     * 
     */
    public void init() throws StoreException {
        log.info("Initalising JDBC3 Store");
        
        Schema.createIfNotExists(ds, BLOB_STORE_TABLE, dialect);
    }

    public String createStoreItem() throws StoreException {
        Connection cn = null;
        PreparedStatement ps = null;
        try {
            cn = ds.getConnection();
            ps = cn.prepareStatement("INSERT INTO BLOB_STORE (ID, DATA) VALUES (?, ?)");
            String id = UUID.randomUUID().toString();
            ps.setString(1, id);
            ps.setBlob(2, new SerialBlob(new byte[0]));
            ps.executeUpdate();
            return id;
        } catch (SQLException e) {
            throw new StoreException(e);
        } finally {
            DB.close(ps, cn);
        }
    }

    public InputStream getInputStream(String id) {
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            cn = ds.getConnection();
            ps = cn.prepareStatement("SELECT DATA FROM BLOB_STORE WHERE ID = ?");
            ps.setString(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Blob b = rs.getBlob(1);
                return b.getBinaryStream();
            } else {
                throw new StoreException("Blob with id %s not found", id);
            }
        } catch (SQLException e) {
            throw new StoreException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }

    public OutputStream getOutputStream(String id) {
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            cn = ds.getConnection();
            ps = cn.prepareStatement("SELECT DATA FROM BLOB_STORE WHERE ID = ? FOR UPDATE");
            ps.setString(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Blob b = rs.getBlob(1);
                return b.setBinaryStream(1);
            } else {
                throw new StoreException("Blob with id %s not found", id);
            }
        } catch (SQLException e) {
            throw new StoreException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }
}
